cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\Nexis downloads"


import excel using "bank80policies-JOE-4-5-2022.xlsx", clear firstrow  allstring
keep ULTIMATEPARENTID lender
rename ULTIMATEPARENTID bank
destring bank, replace
bysort bank: keep if _n==1
save "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data\bank-names", replace


import excel using "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data\all-stories-from-excel-files-Dave.xlsx", clear firstrow  

gen relevant_article=regexm(lower(Relevant),"yes")
keep if !mi(pubid)
keep pubid Headline Publication Date relevant_article
bysort Headline Publication Date: keep if _n==1
save  "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data\relevant_article", replace




foreach i in "109972" "113442" "116103" "118815" "120119" "130851" "138643" /// 
"139639" "17337" "186719" "30255" "32796" "36078" "38741" "38939" "38966" /// 
 "6127" "6541" "6542" "6596" "6827" "6842" "77486" "7827" "7828" "7852" /// 
 "7861" "7886" "7909" "84906" "8861" "8884" {
import excel using `i'.xlsx, clear firstrow
keep Headline Publication Date
gen bank=`i'
save "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data\data`i'.dta", replace
 }
 
import excel using single-story-banks.xlsx, clear firstrow

cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data\"

foreach i in "109972" "113442" "116103" "118815" "120119" "130851" "138643" /// 
"139639" "17337" "186719" "30255" "32796" "36078" "38741" "38939" "38966" /// 
 "6127" "6541" "6542" "6596" "6827" "6842" "77486" "7827" "7828" "7852" /// 
 "7861" "7886" "7909" "84906" "8861" "8884" {
append using "data`i'.dta"
rm "data`i'.dta"
 }
 
replace bank=84906 if bank==138643
 

// add bank names 
sort bank
joinby bank using  bank-names, unm(master)
tab _m
drop _m
rm bank-names.dta


// add filter for relevant article

joinby Headline Publication Date using  relevant_article, unm(both)
tab _m
drop _m
rm relevant_article.dta

keep if relevant_article==1
bysort bank pubid: keep if _n==1
 
save news-bank-data, replace

// Collapse and prepare to merge
use news-bank-data, clear
gen year=year(Date)-1
collapse (sum) relevant_article, by(bank year)
rename bank ultimateparentid
bysort ultimateparentid year: keep if _n==1
save article-bank-year, replace

exit


use news-bank-data, clear

bysort pubid: keep if _n==1

gen national=regexm(Publication,"New York Times") | regexm(Publication,"Guardian")  | regexm(Publication,"Financial Times")

gen local=regexm(Publication,"Times-Tribune") |  regexm(Publication,"Pittsburgh Post-Gazette") |  regexm(Publication,"Pittsburgh Tribune Review") |  regexm(Publication,"Charleston Gazette")

gen x=1
gen year=year(Date)


collapse (sum) x national local, by(year)

keep if year<=2020

// add zero observation pre 2006
append using year-2000-2006.dta
sort year

twoway (scatter   national year, connect(l) lpattern(solid) msymbol(O) color(green)  lwidth(medthick)) /// 
(scatter   local year, connect(l) lpattern(solid) msymbol(O) color(blue)  lwidth(medthick)) ///
 (scatter  x year, connect(l) lpattern(solid) msymbol(O) color(red)  lwidth(medthick)) /// 
 ,scheme(s2mono) graphregion(fcolor(white)) bgcolor(white)  xlabel(2000(2)2020) xtitle("Year") ytitle("Number of Stories") ///
 ylabel(0 (10) 40, nogrid ) yline(0 (10) 40, lpattern(shortdash) lcolor(gs10) lwidth(thin)) ///
 legend( row(2) order(1 2 3) lab(1 "National Newspaper") lab(2 "Local Newspaper") lab(2 "Any") )  ysize(3.75) xsize(5)
 



twoway (scatter  x year, connect(l) lpattern(solid) msymbol(O) color(red)  lwidth(medthick)) /// 
(scatter  loans_mtr_nontr t, connect(l) lpattern(solid) msymbol(O) color(green)  lwidth(medthick)) /// 
  , scheme(s2mono) graphregion(fcolor(white)) bgcolor(white)  xlabel(-5(1)5) xtitle("Event Time") ytitle("Average MTR Loans") ///
    ylabel(0 (0.2) 1.6, nogrid ) xline(0,lpattern(dash) lcolor(blue) lwidth(thin)) ///
	 yline(0 (0.2) 1.6, lpattern(shortdash) lcolor(gs10) lwidth(thin)) ///
   legend( row(1) order(1 2 ) lab(1 "Treated Banks") lab(2 "UnTreated Banks") )  ///
  ysize(3.75) xsize(5)
 
 
 
exit
